﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_XmissionVisitToXmissionAsset')
	DROP PROCEDURE up_XmissionVisitToXmissionAsset

GO

CREATE PROCEDURE [dbo].[up_XmissionVisitToXmissionAsset]
(	
	@vendorProjectId int,
	@feet int
)
AS

DECLARE @UtilityId int
DECLARE @UtilityProjectId int

--Gets the Utility ID
SELECT 
	@UtilityId = UtilityFk
FROM 
	tb_vendor_Projects
WHERE  
	Vendor_ProjectId = @vendorProjectId

--Gets the Utility Project Id
SELECT 
	@UtilityProjectId = uProject.Utility_ProjectId
FROM 
	tb_vendor_Projects vProject
INNER JOIN tb_utility_projects  uProject
	ON vProject.VendorFk = uProject.VendorFk
	AND vProject.ProjectTypeFk = uProject.TypeFk
WHERE  
	vProject.Vendor_ProjectId = @vendorProjectId

/*
UPDATE 
	poles
SET 
	poles.Utility_Asset_XMissionStructures_PoleFk = assets.Utility_Asset_XMissionStructures_PoleID
FROM 
	tb_Vendor_Visit_XmissionStructures_Poles poles
INNER JOIN tb_vendor_projects vendorProjects
	ON vendorProjects.Vendor_ProjectId = poles.Vendor_ProjectFk
INNER JOIN tb_Utility_Asset_XMissionStructures structures
	ON structures.UtilityFk = vendorProjects.UtilityFk
INNER JOIN tb_Utility_Asset_XMissionStructures_Poles assets
	ON assets.Utility_Asset_XMissionStructureFK = structures.Utility_Asset_XMissionStructureId
WHERE
	dbo.fn_DistanceBetween(poles.Latitude, poles.Longitude, assets.Latitude, assets.Longitude) < @feet
	AND poles.Vendor_ProjectFK = @vendorProjectId
	AND poles.Utility_Asset_XMissionStructures_PoleFk IS NULL
*/

PRINT 'Insert Assets from visits'

DECLARE @insertedStructureAssetIds TABLE (Utility_Asset_StructureId int, TransmissionPoleId int)

INSERT INTO tb_Utility_Asset_XMissionStructures
(
	UtilityFK
	,Latitude
	,Longitude
	,Altitude
	,SolutionAvailable
	,XMissionStructures_TopTypeFk
	,Date
	,Utility_ProjectFK
	,HaveImages
)
OUTPUT INSERTED.Utility_Asset_XMissionStructureId, INSERTED.Altitude INTO @insertedStructureAssetIds
SELECT 
	@UtilityId
	,tb_Vendor_Visit_XmissionStructures_Poles.Latitude
	,tb_Vendor_Visit_XmissionStructures_Poles.Longitude
	,Vendor_Visit_XMissionStructures_PoleId
	,1
	,XMissionStructures_PoleTopTypeFk
	,GETDATE()
	,@UtilityProjectId
	,0
FROM 
	tb_Vendor_Visit_XmissionStructures_Poles
INNER JOIN tb_Vendor_Visit_XmissionStructures 
	ON tb_Vendor_Visit_XmissionStructures.Vendor_Visit_XMissionStructureId = tb_Vendor_Visit_XmissionStructures_Poles.Vendor_Visit_XMissionStructureFK
WHERE 
	vendor_projectfk = @vendorProjectId
AND Utility_Asset_XMissionStructures_PoleFk IS NULL

/*
select  top 1 * from tb_Vendor_Visit_XmissionStructures_Poles
*/

DECLARE @insertedAssetIds TABLE (Utility_Asset_TransmissionId int, TransmissionPoleId int)

INSERT INTO tb_Utility_Asset_XMissionStructures_Poles
(
	Utility_Asset_XMissionStructureFK,
	XMissionStructures_PoleTopTypeFk,
	XMissionStructures_PoleSpeciesFk,
	XMissionStructures_PoleTreatmentFk,
	[Year],
	XMissionStructures_PoleClassFk,
	Latitude,
	Longitude,
	Altitude,
	SatelliteCount,
	SatelliteTime,
	SolutionAvailable,
	AID,
	LVisit,
	Number,
	Length,
	LVisitor,
	MapFk,
	SectionFk
)
OUTPUT INSERTED.Utility_Asset_XMissionStructures_PoleId, INSERTED.Altitude INTO @insertedAssetIds
SELECT 
	[AssetStructures].Utility_Asset_StructureId,
	XMissionStructures_PoleTopTypeFk,
	XMissionStructures_PoleSpeciesFk,
	XMissionStructures_PoleTreatmentFk,
	[Year],
	XMissionStructures_PoleClassFk,
	inspPoles.Latitude,
	inspPoles.Longitude,
	Vendor_Visit_XMissionStructures_PoleId,
	inspPoles.SatelliteCount,
	inspPoles.SatelliteTime,
	1,
	NULL,
	LVisit,
	Number,
	Length,
	LVisitor,
	MapFk,
	Section	
FROM 
	tb_Vendor_Visit_XmissionStructures_Poles inspPoles
INNER JOIN @insertedStructureAssetIds [AssetStructures]
	ON [AssetStructures].TransmissionPoleId = inspPoles.Vendor_Visit_XMissionStructures_PoleId
INNER JOIN tb_Vendor_Visit_XmissionStructures 
	ON tb_Vendor_Visit_XmissionStructures.Vendor_Visit_XMissionStructureId = inspPoles.Vendor_Visit_XMissionStructureFK
WHERE 
	vendor_projectfk = @vendorProjectId
AND Utility_Asset_XMissionStructures_PoleFk IS NULL



UPDATE
	tb_Vendor_Visit_XmissionStructures_Poles
SET
	Utility_Asset_XMissionStructures_PoleFk = [InsertedIds].Utility_Asset_TransmissionId
FROM 
	@insertedAssetIds [InsertedIds]
WHERE
	tb_Vendor_Visit_XmissionStructures_Poles.Vendor_Visit_XMissionStructures_PoleId = [InsertedIds].TransmissionPoleId

UPDATE 
	tb_Utility_Asset_XMissionStructures_Poles
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_XMissionStructures_Poles
INNER JOIN @insertedAssetIds [InsertedIds]
	ON tb_Utility_Asset_XMissionStructures_Poles.Utility_Asset_XMissionStructures_PoleId = [InsertedIds].Utility_Asset_TransmissionId

UPDATE 
	tb_Utility_Asset_XMissionStructures
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_XMissionStructures
INNER JOIN @insertedStructureAssetIds [InsertedIds]
	ON [InsertedIds].Utility_Asset_StructureId = tb_Utility_Asset_XMissionStructures.Utility_Asset_XMissionStructureId


IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_XMissionVisitToAsset_UnitTestInitialize')
	DROP PROCEDURE DevOnly_XMissionVisitToAsset_UnitTestInitialize

CREATE proc [dbo].[DevOnly_XMissionVisitToAsset_UnitTestInitialize] 
(
	@dummyUtilityId int output,
	@dummyVendorId int output
)	
AS

SET NOCOUNT ON	
--Creates dummy utility
INSERT INTO tb_Utilities VALUES ('Dummy Utility', 1, 1)
SELECT @dummyUtilityId = @@IDENTITY

--Creates dummy vendor
INSERT INTO tb_Vendors VALUES ('Dummy Vendor', 2, 1)
SELECT @dummyVendorId = @@IDENTITY

--Creates dummy vendor project
INSERT INTO tb_vendor_projects 
(VendorFk, Name, ProjectTypeFk, UtilityFk, ScheduledStartDate, ActualStartDate, ScheduledEndDate, ScheduledCost, ActualCost, TypeFk)
VALUES (@dummyVendorId, 'Dummy distribution project', 1, @dummyUtilityId, GETDATE(), GETDATE(), GETDATE(), 0, 0, 3)

--Creates dummy users
INSERT INTO tb_vendor_contacts VALUES (@dummyVendorId, 1, 'Dummy name', 'dummy.name', 1, 'password', 1)
INSERT INTO tb_utility_contacts VALUES (@dummyUtilityId, 1, 'Dummy name', 'dummy.name', 1, 'password', 1)

--Gets ids 
DECLARE @vendorContactId int
DECLARE @vendorProjectId int

SELECT 
	@vendorContactId = Vendor_ContactId
FROM 
	tb_vendor_contacts
WHERE
	vendorFk = @dummyVendorId

SELECT 
	@vendorProjectId = Vendor_ProjectID
FROM 
	tb_vendor_projects
WHERE
	vendorFk = @dummyVendorId

--Select 20 tx inspc visit and set as dummy insp visits
DECLARE @insertedVisitsStIds TABLE (newVendorVisitStId int, Latitude float, Longitude float)

INSERT INTO tb_Vendor_Visit_XMissionStructures
(
	Vendor_ProjectFk,
	Vendor_ContactFk,
	SolutionAvailable,
	Vendor_Visit_TypeFK,
	Latitude,
	Longitude
) OUTPUT INSERTED.Vendor_Visit_XMissionStructureId, INSERTED.Latitude, INSERTED.Longitude INTO @insertedVisitsStIds
SELECT TOP 20 
	@vendorProjectId,
	@vendorContactId,
	0,
	1,
	Latitude,
	Longitude
FROM 
	tb_Vendor_Visit_XMissionStructures

INSERT INTO tb_Vendor_Visit_XMissionStructures_Poles
(
	Vendor_Visit_XMissionStructureFk 
	,Latitude
	,Longitude
	,LVisit
	,SolutionAvailable
)
SELECT
	newVendorVisitStId,
	Latitude,
	Longitude,
	2009,
	0
FROM
	@insertedVisitsStIds

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_XMissionVisitToAsset_UnitTestCleanUp')
	DROP PROCEDURE DevOnly_XMissionVisitToAsset_UnitTestCleanUp

CREATE proc [dbo].[DevOnly_XMissionVisitToAsset_UnitTestCleanUp] (
	@dummyUtilityId int,
	@dummyVendorId int
)
AS
/* 	
	Purpose: Clears tables used to test 
*/

SET NOCOUNT ON	

--Delete Tx visits
DELETE tb_Vendor_Visit_XMissionStructures_Poles
WHERE Vendor_Visit_XMissionStructureFk IN 
	(	
		SELECT Vendor_Visit_XMissionStructureId 
		FROM tb_Vendor_Visit_XMissionStructures st
		INNER JOIN tb_Vendor_Projects pr
			ON st.Vendor_ProjectFk = pr.Vendor_ProjectId
		WHERE pr.VendorFk = @dummyVendorId
	)

DELETE tb_Vendor_Visit_XMissionStructures
WHERE Vendor_ProjectFk IN (SELECT Vendor_ProjectId FROM tb_Vendor_Projects WHERE VendorFk = @dummyVendorId)

DELETE tb_Utility_Asset_XmissionStructures_Poles
WHERE Utility_Asset_XMissionStructureFK IN (SELECT Utility_Asset_XMissionStructureId FROM tb_Utility_Asset_XMissionStructures WHERE UtilityFk = @dummyUtilityId)

DELETE tb_Utility_Asset_XMissionStructures
WHERE UtilityFk = @dummyUtilityId

--Delete users
DELETE tb_vendor_contacts 
WHERE VendorFk = @dummyVendorId

DELETE tb_utility_contacts 
WHERE UtilityFk = @dummyUtilityId

--Delete the project
DELETE tb_vendor_projects
WHERE VendorFk = @dummyVendorId

--Deletes the utility
DELETE tb_Utilities 
WHERE UtilityId = @dummyUtilityId

--Deletes the vendor
DELETE tb_Vendors
WHERE VendorId = @dummyVendorId

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_XMissionVisitToAsset_UnitTest')
	DROP PROCEDURE DevOnly_XMissionVisitToAsset_UnitTest
	
CREATE proc [dbo].[DevOnly_XMissionVisitToAsset_UnitTest] as
/* 	
	Purpose: Tests stored procedures 
*/
SET NOCOUNT ON	

DECLARE @dummyUtilityId int
DECLARE @dummyVendorId int
DECLARE @x varchar(100)
DECLARE @y varchar(100)

--Initialize data to test
PRINT 'Initializing data to test'
EXEC DevOnly_XMissionVisitToAsset_UnitTestInitialize @dummyUtilityId OUTPUT, @dummyVendorId OUTPUT

PRINT 'Dummy utility ID: ' + CONVERT(varchar, @dummyUtilityId)
PRINT 'Dummy vendor ID: ' + CONVERT(varchar, @dummyVendorId)

-- bail on error
if (NOT @@error = 0)
	return(1)

--Executes tests
DECLARE @vendorProjectId int
SELECT @vendorProjectId = Vendor_ProjectId FROM tb_vendor_projects WHERE UtilityFk = @dummyUtilityId

--Gets count of assets that should be created
DECLARE @assetsCount int
DECLARE @visitCount int

SELECT @visitCount = COUNT(*) 
FROM tb_Vendor_Visit_XmissionStructures_Poles Visits
WHERE Vendor_Visit_XMissionStructureFk IN 
	(	
		SELECT Vendor_Visit_XMissionStructureId 
		FROM tb_Vendor_Visit_XMissionStructures st
		INNER JOIN tb_Vendor_Projects pr
			ON st.Vendor_ProjectFk = pr.Vendor_ProjectId
		WHERE pr.VendorFk = @dummyVendorId
	)

--Create assets from visits
EXEC up_XmissionVisitToXmissionAsset @vendorProjectId, 20

SELECT @assetsCount = COUNT(*) 
FROM tb_Utility_Asset_XMissionStructures_Poles poles
INNER JOIN tb_Utility_Asset_XMissionStructures st
	ON poles.Utility_Asset_XMissionStructureFK = st.Utility_Asset_XMissionStructureId
WHERE st.UtilityFk = @dummyUtilityId

if (@assetsCount <> @visitCount)
begin
	set @x = 'Assets created: ' + convert(varchar(100), @assetsCount)
	set @y = 'Visits should be converted: ' + convert(varchar(100), @visitCount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'Visits used and assets created should be equal.')
end

SELECT @visitCount = COUNT(*) 
FROM tb_Vendor_Visit_XmissionStructures_Poles Visits
WHERE Visits.Vendor_Visit_XMissionStructureFk IN 
	(	
		SELECT Vendor_Visit_XMissionStructureId 
		FROM tb_Vendor_Visit_XMissionStructures st
		INNER JOIN tb_Vendor_Projects pr
			ON st.Vendor_ProjectFk = pr.Vendor_ProjectId
		WHERE pr.VendorFk = @dummyVendorId
	)
AND Visits.Utility_Asset_XMissionStructures_PoleFk = NULL

if (@visitCount <> 0)
begin
	set @y = convert(varchar(100), @visitCount)
	set @x = convert(varchar(100), 0)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'Problem in visits asset foreign key, it shouldnt be null')
end

--If I execute it again no assets should be created
--Gets poles converted to compare later
DECLARE @visitsConverted table (idVisit int, idAsset int)

INSERT INTO @visitsConverted
(
	idVisit,
	idAsset
)
SELECT Vendor_Visit_XMissionStructures_PoleId, Utility_Asset_XMissionStructures_PoleFk
FROM tb_Vendor_Visit_XmissionStructures_Poles poles
WHERE poles.Vendor_Visit_XMissionStructureFk IN 
	(	
		SELECT Vendor_Visit_XMissionStructureId 
		FROM tb_Vendor_Visit_XMissionStructures st
		INNER JOIN tb_Vendor_Projects pr
			ON st.Vendor_ProjectFk = pr.Vendor_ProjectId
		WHERE pr.VendorFk = @dummyVendorId
	)
AND poles.Utility_Asset_XMissionStructures_PoleFk IS NOT NULL

EXEC up_XmissionVisitToXmissionAsset @vendorProjectId, 20

SELECT @visitCount = COUNT(*)
FROM tb_Vendor_Visit_XmissionStructures_Poles Visits
INNER JOIN @visitsConverted visitsConverted
	ON Visits.Vendor_Visit_XMissionStructures_PoleId = VisitsConverted.idVisit
	AND Visits.Utility_Asset_XMissionStructures_PoleFk = VisitsConverted.idAsset

DECLARE @originalPoles int

SELECT @originalPoles = COUNT(*) FROM @visitsConverted

if (@originalPoles <> @visitCount)
begin
	set @y = convert(varchar(100), @visitCount)
	set @x = convert(varchar(100), @originalPoles)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'New Assets where created. It shouldnt happened')
end


--Deletes data used
PRINT 'Deleting data used to test'
EXEC DevOnly_XMissionVisitToAsset_UnitTestCleanUp @dummyUtilityId, @dummyVendorId

-- bail on error
if (NOT @@error = 0)
	return(1)


GO






 